package impl;

import bean.UserInfo;
import dao.UserDao;
import util.DBHelper;
import util.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @classname:UserDaoImpl
 * @description:
 * @author: Mr.Z
 * @date: 2020/12/29 20:13
 * @version: 0.1
 * @since:1.8
 */
public class UserDaoImpl implements UserDao {
    private Connection conn;
    private PreparedStatement ps;
    private String sql;
    private int result;
    private ResultSet rs;

    @Override
    public int add(UserInfo userInfo) {
        conn = DBHelper.getCon();
        sql = "insert into tb_userinfo(name,age,balance,image,birthday,roleid,status) value (?,?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, userInfo.getName());
            ps.setObject(2, userInfo.getAge());
            ps.setObject(3, userInfo.getBalance());
            ps.setObject(4, userInfo.getImage());
            ps.setObject(5, userInfo.getBirthday());
            ps.setObject(6, userInfo.getRoleid());
            ps.setObject(7, userInfo.getStatus());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }

        return result;
    }

    @Override
    public int delete(int id) {
        conn = DBHelper.getCon();

        sql = "delete from tb_userinfo where id=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }

        return result;
    }

    @Override
    public int deleteMany(int[] ids) {
        conn = DBHelper.getCon();
        StringBuilder builder = new StringBuilder();
        for (int id : ids) {
            builder.append(id);
            builder.append(",");
        }
        builder.deleteCharAt(builder.lastIndexOf(","));

        sql = "delete from tb_userinfo where id in(" + builder + ")";
        try {
            ps = conn.prepareStatement(sql);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }

        return result;
    }

    @Override
    public UserInfo selectUserById(int id) {
        conn = DBHelper.getCon();
        sql = "select * from tb_userinfo where id=?";
        UserInfo user =null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()){
                user = new UserInfo(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public long countUserInfo() {
        conn = DBHelper.getCon();
        sql = "select count(*) from tb_userinfo";
        long num = 0;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
               num = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn,ps,rs);
        }

        return num;
    }

    @Override
    public List<UserInfo> selectUserInfoByPage(int page) {
        conn = DBHelper.getCon();
        sql = "select * from tb_userinfo limit ?,?";
        List<UserInfo> list = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*Page.PAGE_SIZE);
            ps.setInt(2,Page.PAGE_SIZE);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(new UserInfo(rs));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn,ps,rs);
        }


        return list;
    }
}
